﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace TST.SharePointObjects
{
    public class ObjectReferenceStore
    {
        private Guid _site;
        private Guid _web;
        //private SqlDataAdapter _adapter;
        //private SqlConnection _connection;
        private LicensingMode _licensingMode;
        private SharePointObjectsDatabase _database;

        public ObjectReferenceStore(Guid siteID, Guid webID, LicensingMode licensingMode, SharePointObjectsDatabase database)
        {
            _site = siteID;
            _web = webID;
            _licensingMode = licensingMode;
            _database = database;
        }

        private DataTable GetObjectReferences(ObjectReferenceType referenceType, int maxRowCount)
        {
            return _database.GetData(GetSelectCommand(referenceType, maxRowCount), GetInsertCommand(), GetUpdateCommand(), false);
            //return _database.GetData(GetSelectCommand(_connection, referenceType), GetInsertCommand(_connection), GetUpdateCommand(_connection));
            //_connection = Database.Connection;

            //DataSet data = new DataSet();
            //try
            //{
            //    //_connection.Open();
            //    _adapter = new SqlDataAdapter(GetSelectCommand(_connection, referenceType));
            //    _adapter.InsertCommand = GetInsertCommand(_connection);
            //    _adapter.UpdateCommand = GetUpdateCommand(_connection);
            //    _adapter.Fill(data);
            //    return data.Tables[0];
            //}
            //catch
            //{
            //    if (_adapter != null)
            //    {
            //        _adapter.Dispose();
            //    }
            //    if (_connection != null)
            //    {
            //        if (_connection.State == ConnectionState.Open)
            //        {
            //            _connection.Close();
            //        }
            //        _connection.Dispose();
            //    }
            //    return null;
            //}
        }

        internal ObjectReferenceList GetObjectReferences(ObjectReferenceSelection selection, int maxRowCount)
        {
            if (selection == null)
                return null;

            DataTable table = GetObjectReferencesAsTable(selection, false, maxRowCount);
            if (table == null)
                return null;

            ObjectReferenceList result = new ObjectReferenceList(_licensingMode);
            if (result != null)
            {
                foreach (DataRow row in table.Rows)
                {
                    ObjectReference reference = new ObjectReference(row);
                    result.Add(reference);
                }
            }
            return result;
        }

        internal DataTable GetObjectReferencesAsTable(ObjectReferenceSelection selection, Boolean expandData, int maxRowCount)
        {
            SqlCommand selectCommand = new SqlCommand();
            selectCommand.CommandType = CommandType.Text;

            StringBuilder commandBuilder = new StringBuilder();
            commandBuilder.AppendFormat("select top {0} ReferenceType, ReferenceID, ObjectID, Name, Url, SiteTitle, SiteUrl, ListTitle, ListUrl from ObjectReferences", maxRowCount);

            bool addedScope = false;
            switch (selection.Scope)
            {
                case ObjectReferenceScope.Web:
                    commandBuilder.Append(" where WebID=@WebID");
                    SqlParameter paramWebID = new SqlParameter("@WebID", SqlDbType.UniqueIdentifier);
                    paramWebID.Value = _web;
                    selectCommand.Parameters.Add(paramWebID);
                    addedScope = true;
                    break;
                case ObjectReferenceScope.SiteCollection:
                    commandBuilder.Append(" where SiteID=@SiteID");
                    SqlParameter paramSiteID = new SqlParameter("@SiteID", SqlDbType.UniqueIdentifier);
                    paramSiteID.Value = _site;
                    selectCommand.Parameters.Add(paramSiteID);
                    addedScope = true;
                    break;
            }

            if (addedScope)
            {
                commandBuilder.Append(" and ReferenceID=@ReferenceID");
            }
            else
            {
                commandBuilder.Append(" where ReferenceID=@ReferenceID");
            }
            SqlParameter paramReferenceID = new SqlParameter("@ReferenceID", SqlDbType.NVarChar, 512);
            paramReferenceID.Value = selection.ObjectId;
            selectCommand.Parameters.Add(paramReferenceID);

            if (selection.ReferenceType != ObjectReferenceType.Unknown)
            {
                commandBuilder.Append(" and ReferenceType=@ReferenceType");
                SqlParameter paramReferenceType = new SqlParameter("@ReferenceType", SqlDbType.TinyInt);
                paramReferenceType.Value = (int)selection.ReferenceType;
                selectCommand.Parameters.Add(paramReferenceType);
            }

            if (!string.IsNullOrEmpty(selection.Search))
            {
                commandBuilder.AppendFormat(" and Name like '%{0}%'", selection.Search);
            }
            commandBuilder.Append(" Order By ReferenceType, Name");

            selectCommand.CommandText = commandBuilder.ToString();
            DataTable table = _database.GetData(selectCommand, CommandType.Text);
            if (expandData)
            {
                table.Columns.Add(new DataColumn("ReferenceTypeDescription", typeof(String)));
                foreach (DataRow row in table.Rows)
                {
                    ObjectReferenceType refType = ObjectReferenceType.Unknown;
                    if (row["ReferenceType"] != null && row.Table.Columns.Contains("ReferenceType"))
                    {
                        int r = 0;
                        if (int.TryParse(row["ReferenceType"].ToString(), out r))
                            refType = (ObjectReferenceType)Enum.ToObject(typeof(ObjectReferenceType), r);
                        else
                            refType = ObjectReferenceType.Unknown;
                        row["ReferenceTypeDescription"] = Globals.GetReferenceTypeDescription(refType);
                    }
                }
            }
            return table;
        }

        public int UpdateObjectReferences(DataTable data)
        {
            return _database.UpdateData(data);
        }

        private SqlCommand GetUpdateCommand()
        {
            // Setup the UPDATE command
            SqlCommand updateCommand = new SqlCommand();
            updateCommand.CommandType = CommandType.Text;
            updateCommand.UpdatedRowSource = UpdateRowSource.None;
            //updateCommand.Connection = connection;
            updateCommand.CommandText = string.Format("update ObjectReferences set Name = @Name, Url=@Url, SiteTitle=@SiteTitle, SiteUrl=@SiteUrl, ListTitle=@ListTitle, ListUrl=@ListUrl where SiteID='{0}' and WebID='{1}' and ReferenceType=@ReferenceType and ReferenceID=@ReferenceID and ObjectID=@ObjectID", _site, _web);

            SqlParameter paramName = new SqlParameter("@Name", SqlDbType.NVarChar, 255);
            paramName.SourceColumn = "Name";
            updateCommand.Parameters.Add(paramName);

            SqlParameter paramObjectID = new SqlParameter("@ObjectID", SqlDbType.NVarChar, 512);
            paramObjectID.SourceColumn = "ObjectID";
            updateCommand.Parameters.Add(paramObjectID);

            SqlParameter paramReferenceID = new SqlParameter("@ReferenceID", SqlDbType.NVarChar, 512);
            paramReferenceID.SourceColumn = "ReferenceID";
            updateCommand.Parameters.Add(paramReferenceID);

            SqlParameter paramReferenceType = new SqlParameter("@ReferenceType", SqlDbType.NVarChar, 255);
            paramReferenceType.SourceColumn = "ReferenceType";
            updateCommand.Parameters.Add(paramReferenceType);

            SqlParameter paramUrl = new SqlParameter("@Url", SqlDbType.NVarChar, 255);
            paramUrl.SourceColumn = "Url";
            updateCommand.Parameters.Add(paramUrl);

            SqlParameter paramSiteTitle = new SqlParameter("@SiteTitle", SqlDbType.NVarChar, 255);
            paramSiteTitle.SourceColumn = "SiteTitle";
            updateCommand.Parameters.Add(paramSiteTitle);

            SqlParameter paramSiteUrl = new SqlParameter("@SiteUrl", SqlDbType.NVarChar, 255);
            paramSiteUrl.SourceColumn = "SiteUrl";
            updateCommand.Parameters.Add(paramSiteUrl);

            SqlParameter paramListTitle = new SqlParameter("@ListTitle", SqlDbType.NVarChar, 255);
            paramListTitle.SourceColumn = "ListTitle";
            updateCommand.Parameters.Add(paramListTitle);

            SqlParameter paramListUrl = new SqlParameter("@ListUrl", SqlDbType.NVarChar, 255);
            paramListUrl.SourceColumn = "ListUrl";
            updateCommand.Parameters.Add(paramListUrl);

            return updateCommand;
        }

        private SqlCommand GetInsertCommand()
        {
            // Setup the INSERT command
            SqlCommand insertCommand = new SqlCommand();
            insertCommand.CommandType = CommandType.Text;
            insertCommand.UpdatedRowSource = UpdateRowSource.None;
            //insertCommand.Connection = connection;
            insertCommand.CommandText = string.Format("insert into ObjectReferences (SiteID, WebID, ReferenceID, ObjectID, Name, ReferenceType, Url, SiteTitle, SiteUrl, ListTitle, ListUrl) values (@SiteID, @WebID, @ReferenceID, @ObjectID, @Name, @ReferenceType, @Url, @SiteTitle, @SiteUrl, @ListTitle, @ListUrl)");

            SqlParameter insertParamSiteID = new SqlParameter("@SiteID", SqlDbType.UniqueIdentifier);
            insertParamSiteID.Value = _site;
            insertCommand.Parameters.Add(insertParamSiteID);

            SqlParameter insertParamWebID = new SqlParameter("@WebID", SqlDbType.UniqueIdentifier);
            insertParamWebID.Value = _web;
            insertCommand.Parameters.Add(insertParamWebID);

            SqlParameter insertParamReferenceID = new SqlParameter("@ReferenceID", SqlDbType.NVarChar, 512);
            insertParamReferenceID.SourceColumn = "ReferenceID";
            insertCommand.Parameters.Add(insertParamReferenceID);

            SqlParameter insertParamObjectType = new SqlParameter("@ReferenceType", SqlDbType.TinyInt);
            insertParamObjectType.SourceColumn = "ReferenceType";
            insertCommand.Parameters.Add(insertParamObjectType);

            SqlParameter insertParamName = new SqlParameter("@Name", SqlDbType.NVarChar, 255);
            insertParamName.SourceColumn = "Name";
            insertCommand.Parameters.Add(insertParamName);

            SqlParameter insertParamObjectID = new SqlParameter("@ObjectID", SqlDbType.NVarChar, 512);
            insertParamObjectID.SourceColumn = "ObjectID";
            insertCommand.Parameters.Add(insertParamObjectID);

            SqlParameter insertParamUrl = new SqlParameter("@Url", SqlDbType.NVarChar, 255);
            insertParamUrl.SourceColumn = "Url";
            insertCommand.Parameters.Add(insertParamUrl);

            SqlParameter paramSiteTitle = new SqlParameter("@SiteTitle", SqlDbType.NVarChar, 255);
            paramSiteTitle.SourceColumn = "SiteTitle";
            insertCommand.Parameters.Add(paramSiteTitle);

            SqlParameter paramSiteUrl = new SqlParameter("@SiteUrl", SqlDbType.NVarChar, 255);
            paramSiteUrl.SourceColumn = "SiteUrl";
            insertCommand.Parameters.Add(paramSiteUrl);

            SqlParameter paramListTitle = new SqlParameter("@ListTitle", SqlDbType.NVarChar, 255);
            paramListTitle.SourceColumn = "ListTitle";
            insertCommand.Parameters.Add(paramListTitle);

            SqlParameter paramListUrl = new SqlParameter("@ListUrl", SqlDbType.NVarChar, 255);
            paramListUrl.SourceColumn = "ListUrl";
            insertCommand.Parameters.Add(paramListUrl);

            return insertCommand;
        }

        private SqlCommand GetSelectCommand(ObjectReferenceType referenceType, int maxRowCount)
        {
            SqlCommand selectCommand = new SqlCommand();
            //selectCommand.Connection = connection;
            selectCommand.CommandType = CommandType.Text;
            if (maxRowCount == -1)
            {
                selectCommand.CommandText = "select ReferenceType, ReferenceID, ObjectID, Name, Url, SiteTitle, SiteUrl, ListTitle, ListUrl from ObjectReferences where SiteID=@SiteID and WebID=@WebID and ReferenceType=@ReferenceType Order By ReferenceID, ObjectID";
            }
            else
            {
                selectCommand.CommandText = string.Format("select top {0} ReferenceType, ReferenceID, ObjectID, Name, Url, SiteTitle, SiteUrl, ListTitle, ListUrl from ObjectReferences where SiteID=@SiteID and WebID=@WebID and ReferenceType=@ReferenceType Order By ReferenceType, Name", maxRowCount);
            }
            SqlParameter paramSiteID = new SqlParameter("@SiteID", SqlDbType.UniqueIdentifier);
            paramSiteID.Value = _site;
            selectCommand.Parameters.Add(paramSiteID);

            SqlParameter paramWebID = new SqlParameter("@WebID", SqlDbType.UniqueIdentifier);
            paramWebID.Value = _web;
            selectCommand.Parameters.Add(paramWebID);

            SqlParameter paramReferenceType = new SqlParameter("@ReferenceType", SqlDbType.TinyInt);
            paramReferenceType.Value = (int)referenceType;
            selectCommand.Parameters.Add(paramReferenceType);

            return selectCommand;
        }

        internal DataTable SaveObjectReferences(ObjectReferenceList referenceList)
        {
            DataTable indexData = _database.GetData(GetSelectCommand(referenceList.ObjectReferenceType, -1), GetInsertCommand(), GetUpdateCommand(), false);
            //DataTable indexData = GetObjectReferences(referenceList.ObjectReferenceType);
            bool update = false;
            foreach (ObjectReference reference in referenceList.Items)
            {
                DataRow[] rows = indexData.Select(string.Format("ReferenceID='{1}' and ObjectID='{0}'", reference.Id, reference.ObjectId), "Name", DataViewRowState.OriginalRows);
                if (rows.Length == 0)
                {
                    DataRow newRow = indexData.NewRow();
                    newRow["ReferenceID"] = reference.ObjectId;
                    newRow["ObjectID"] = reference.Id;
                    newRow["Name"] = reference.Name;
                    newRow["ReferenceType"] = (int)reference.ObjectReferenceType;
                    newRow["Url"] = reference.Url;
                    newRow["SiteTitle"] = reference.SiteTitle;
                    newRow["SiteUrl"] = reference.SiteUrl;
                    newRow["ListTitle"] = reference.ListTitle;
                    newRow["ListUrl"] = reference.ListUrl;
                    indexData.Rows.Add(newRow);
                    update = true;
                }
                else
                {
                    foreach (DataRow row in rows)
                    {
                        if (row["Name"].ToString() != reference.Name || row["Url"].ToString() != reference.Url ||
                            row["SiteTitle"].ToString() != reference.SiteTitle || row["SiteUrl"].ToString() != reference.SiteUrl ||
                            row["ListTitle"].ToString() != reference.ListTitle || row["ListUrl"].ToString() != reference.ListUrl)
                        {
                            row.BeginEdit();
                            row["Name"] = reference.Name;
                            row["Url"] = reference.Url;
                            row["SiteTitle"] = reference.SiteTitle;
                            row["SiteUrl"] = reference.SiteUrl;
                            row["ListTitle"] = reference.ListTitle;
                            row["ListUrl"] = reference.ListUrl;
                            row.EndEdit();
                            update = true;
                        }
                    }
                }
            }
            if (update)
            {
                int updated = UpdateObjectReferences(indexData);
            }
            return indexData;
        }

        internal static void DeleteObjectReferences(Guid webApplicationID, SharePointObjectsDatabase database)
        {
            // Setup the DELETE command
            SqlCommand procedure = new SqlCommand();
            procedure.CommandType = CommandType.StoredProcedure;
            procedure.UpdatedRowSource = UpdateRowSource.None;
            procedure.CommandText = "DeleteObjectReferencesById";

            SqlParameter deleteParamSiteID = new SqlParameter("@ReferenceID", SqlDbType.NVarChar, 512);
            deleteParamSiteID.Value = webApplicationID.ToString("B");
            procedure.Parameters.Add(deleteParamSiteID);

            database.RunStoredProcedure(procedure);
        }

        internal static void DeleteObjectReferences(Guid siteID, Guid webID, SharePointObjectsDatabase database)
        {
            // Setup the DELETE command
            SqlCommand procedure = new SqlCommand();
            procedure.CommandType = CommandType.StoredProcedure;
            procedure.UpdatedRowSource = UpdateRowSource.None;
            procedure.CommandText = "DeleteObjectReferences";

            SqlParameter insertParamSiteID = new SqlParameter("@SiteID", SqlDbType.UniqueIdentifier);
            insertParamSiteID.Value = siteID;
            procedure.Parameters.Add(insertParamSiteID);

            SqlParameter insertParamWebID = new SqlParameter("@WebID", SqlDbType.UniqueIdentifier);
            insertParamWebID.Value = webID;
            procedure.Parameters.Add(insertParamWebID);

            database.RunStoredProcedure(procedure);
        }

    }

    public enum ObjectReferenceType
    {
        Unknown = 0,
        ContentTypeList = 1,
        ContentTypeListItem = 2,
        SiteColumnList = 3,
        SiteColumnListItem = 4,
        SiteColumnContentType = 5,
        ContentTypeParent = 6,
        SharePointGroupWeb = 7,
        SharePointGroupList = 8,
        SharePointGroupFolder = 9,
        SharePointGroupItem = 10,
        FeatureDefinitionFarm = 11,
        FeatureDefinitionWebApplication = 12,
        FeatureDefinitionSite = 13,
        FeatureDefinitionWeb = 14
    }
}
